package orm;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import web_wuyu.bean.User;

public class DBhelp {
	private Connection getConn() {
		String driver = "com.mysql.jdbc.Driver";
		String url = "jdbc:mysql://localhost:3306/mybase?useUnicode=true&characterEncoding=utf-8&useSSL=false";
		String username = "root";
		String password = "1234";
		Connection conn = null;
		try {
			Class.forName(driver);
			conn = (Connection) DriverManager.getConnection(url, username, password);
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		return conn;
	}

	public int insert(User user) {
		Connection conn = getConn();
		int i = 0;
		String sql = "insert into user (Name,Age) values(?,?)";
		PreparedStatement pstmt;
		try {
			pstmt = (PreparedStatement) conn.prepareStatement(sql);
			pstmt.setString(1, user.getName());
			pstmt.setInt(2, user.getAge());
			i = pstmt.executeUpdate();
			System.out.println("insert: " + i);
			pstmt.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return i;
	}

	public int delete(String name) {
		Connection conn = getConn();
		int i = 0;
		String sql = "delete from user where Name='" + name + "'";
		PreparedStatement pstmt;
		try {
			pstmt = (PreparedStatement) conn.prepareStatement(sql);
			i = pstmt.executeUpdate();
			System.out.println("delete: " + i);
			pstmt.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return i;
	}

	public int update(User t) {
		Connection conn = getConn();
		int i = 0;
		String sql = "update user set Age=" + t.getAge() + " where Name='" + t.getName() + "'";
		PreparedStatement pstmt;
		try {
			pstmt = (PreparedStatement) conn.prepareStatement(sql);
			i = pstmt.executeUpdate();
			System.out.println("update : " + i);
			pstmt.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return i;
	}
	 public User getOne(int id){  
	    	Connection conn = getConn();
	        String sql = "select id,name,age from user where id=?";
	        PreparedStatement pstmt;
	        try {
	            pstmt = (PreparedStatement)conn.prepareStatement(sql);
	            pstmt.setInt(1, id);
	            ResultSet rs = pstmt.executeQuery();   
	            User u=new User();
	            if (rs.next()) {           
	            
	                u.setId(rs.getInt(1));
	                u.setName(rs.getString(2));
	                u.setAge(rs.getInt(3));              
	            }
	            System.out.println("==========getOne===begin===============");
	            System.out.println("======u.id=="+u.getId()+"===========");
	            System.out.println("======u.name=="+u.getName()+"===========");
	            System.out.println("======u.age=="+u.getAge()+"===========");
	            System.out.println("==========getOne===end===============");
	        } catch (SQLException e) {
	            e.printStackTrace();
	        }
	        return null;
	    }
	 public   Integer getAll() {
	        Connection conn = getConn();
	        String sql = "select * from user";
	        PreparedStatement pstmt;
	        try {
	            pstmt = (PreparedStatement)conn.prepareStatement(sql);
	            ResultSet rs = pstmt.executeQuery();
	            int col = rs.getMetaData().getColumnCount();
	            System.out.println("=============getAll begin===============");
	            System.out.println("name,	age,	group,	id");
	            
	            while (rs.next()) {
	                for (int i = 1; i <= col; i++) {
	                    System.out.print(rs.getString(i) + "\t");
	                    if ((i == 2) && (rs.getString(i).length() < 8)) {
	                        System.out.print("\t");
	                    }
	                 }
	                System.out.println("");
	            }
	                System.out.println("============getAll end================");
	        } catch (SQLException e) {
	            e.printStackTrace();
	        }
	        return null;
	    }
}
